﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Meeting.DBUtility;
using Newtonsoft.Json;
using System.Data.SqlClient;
using Meeting.Model;
using Newtonsoft.Json.Converters;
using System.Collections;

namespace Meeting.BLL
{
    /// <summary>
    /// 模块作者：许文亭
    /// 模块日期：2013-1-3
    /// 模块功能：投票表vote 增删查改
    /// </summary>
    public class Vote
    {
        public string errorString = "";

        string insertVoteItem = "INSERT INTO [dbo].[VoteItem]([VoteId],[Value],[Sort])VALUES({0},'{1}',1)";
        string updateVoteItem = "UPDATE VoteItem Set Value = '{1}' where Id = {0}";
        string delVoteItemList = "delete from VoteItem where Id in({0})";
        //获取投票表数据
        private DataSet GetVotes(string filter)
        {
            string sql = "Select * From Vote Where 1=1 ";
            if (!string.IsNullOrEmpty(filter)) sql += " And " + filter;
            try
            {
                return DbHelperSQL.Query(sql);
            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                return null;
            }
        }

        /// <summary>
        /// 获取单个实体类
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public string GetBy(int id)
        {
            try
            {
                DataSet ds = GetVotes(" Id=" + id);
                if (ds == null || ds.Tables[0].Rows.Count == 0) return "";

                Model.Vote model = new Model.Vote();
                if (ds.Tables[0].Rows[0]["Id"] != null && ds.Tables[0].Rows[0]["Id"].ToString() != "")
                {
                    model.Id = int.Parse(ds.Tables[0].Rows[0]["Id"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Theme"] != null && ds.Tables[0].Rows[0]["Theme"].ToString() != "")
                {
                    model.Theme = ds.Tables[0].Rows[0]["Theme"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Category"] != null && ds.Tables[0].Rows[0]["Category"].ToString() != "")
                {
                    model.Category = int.Parse(ds.Tables[0].Rows[0]["Category"].ToString());
                }
                if (ds.Tables[0].Rows[0]["EmployeeId"] != null && ds.Tables[0].Rows[0]["EmployeeId"].ToString() != "")
                {
                    model.EmployeeId = ds.Tables[0].Rows[0]["EmployeeId"].ToString();
                }
                if (ds.Tables[0].Rows[0]["EmployeeName"] != null && ds.Tables[0].Rows[0]["EmployeeName"].ToString() != "")
                {
                    model.EmployeeName = ds.Tables[0].Rows[0]["EmployeeName"].ToString();
                }
                if (ds.Tables[0].Rows[0]["RoleId"] != null && ds.Tables[0].Rows[0]["RoleId"].ToString() != "")
                {
                    model.RoleId = ds.Tables[0].Rows[0]["RoleId"].ToString();
                }
                if (ds.Tables[0].Rows[0]["CreateDate"] != null && ds.Tables[0].Rows[0]["CreateDate"].ToString() != "")
                {
                    model.CreateDate = DateTime.Parse(ds.Tables[0].Rows[0]["CreateDate"].ToString());
                }
                if (ds.Tables[0].Rows[0]["StartDate"] != null && ds.Tables[0].Rows[0]["StartDate"].ToString() != "")
                {
                    model.StartDate = DateTime.Parse(ds.Tables[0].Rows[0]["StartDate"].ToString());
                }
                if (ds.Tables[0].Rows[0]["EndDate"] != null && ds.Tables[0].Rows[0]["EndDate"].ToString() != "")
                {
                    model.EndDate = DateTime.Parse(ds.Tables[0].Rows[0]["EndDate"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Comment"] != null && ds.Tables[0].Rows[0]["Comment"].ToString() != "")
                {
                    model.Comment = ds.Tables[0].Rows[0]["Comment"].ToString();
                }
                if (ds.Tables[0].Rows[0]["IsActive"] != null && ds.Tables[0].Rows[0]["IsActive"].ToString() != "")
                {
                    if ((ds.Tables[0].Rows[0]["IsActive"].ToString() == "1") || (ds.Tables[0].Rows[0]["IsActive"].ToString().ToLower() == "true"))
                    {
                        model.IsActive = true;
                    }
                    else
                    {
                        model.IsActive = false;
                    }
                }

                Newtonsoft.Json.Converters.IsoDateTimeConverter timeConverter = new Newtonsoft.Json.Converters.IsoDateTimeConverter();
                timeConverter.DateTimeFormat = "yyyy'-'MM'-'dd' 'HH':'mm':'ss";

                return JsonConvert.SerializeObject(model, timeConverter);
            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                return "";
            }
        }

        /// <summary>
        /// 获取投票下的选项
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public string GetItemsBy(int id)
        {
            try
            {
                DataSet ds = DbHelperSQL.Query("SELECT [Id],[VoteId],[Sort],[Value] FROM [dbo].[VoteItem] where VoteId = " + id);
                //
                if (ds == null) return "";
                //jsonresout
                Model.JsonGrid jr = new Model.JsonGrid();
                jr.total = ds.Tables[0].Rows.Count;
                jr.rows = ds.Tables[0];
                return JsonConvert.SerializeObject(jr);
            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                return "";
            }
        }

        /// <summary>
        /// 获取分页数据
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="filter"></param>
        /// <returns></returns>
        public string GetPage(int pageIndex, int pageSize, string filter)
        {
            int nAllRecord = 0, nAllPages = 0;

            // DataTable dt = DbHelperSQL.GetPage("id", " * ", "rssNews", "", " pubDate Desc ", pageIndex, pageSize, out nAllRecord, out nAllPages);
            DataTable dt = DbHelperSQL.GetPage("Id", " * ", " dbo.Vote ", filter, " Theme ", pageIndex, pageSize, out nAllRecord, out nAllPages);
            if (dt == null) return "";
            JsonGrid jr = new JsonGrid();
            jr.total = nAllRecord;
            jr.rows = dt;

            Newtonsoft.Json.Converters.IsoDateTimeConverter timeConverter = new Newtonsoft.Json.Converters.IsoDateTimeConverter();
            timeConverter.DateTimeFormat = "yyyy'-'MM'-'dd' 'HH':'mm':'ss";

            return JsonConvert.SerializeObject(jr, timeConverter);
            //return Json.DataTableToJson(dt, nAllRecord);
        }

        /// <summary>
        /// 保存投票信息
        /// </summary>
        public int Add(Meeting.Model.Vote model, List<Model.VoteItem> itemList, List<Model.VoteSubOffice> subOffices)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into Vote(");
            strSql.Append("Theme,Category,EmployeeId,EmployeeName,RoleId,StartDate,EndDate,CreateDate,Comment,IsActive,EmployeeTitle)");
            strSql.Append(" values (");
            strSql.Append("@Theme,@Category,@EmployeeId,@EmployeeName,@RoleId,@StartDate,@EndDate,@CreateDate,@Comment,@IsActive,@EmployeeTitle)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@Theme", SqlDbType.NVarChar,128),
					new SqlParameter("@Category", SqlDbType.Int,4),
					new SqlParameter("@EmployeeId", SqlDbType.NVarChar,64),
					new SqlParameter("@EmployeeName", SqlDbType.NVarChar,64),
					new SqlParameter("@RoleId", SqlDbType.NVarChar,1024),
					new SqlParameter("@StartDate", SqlDbType.DateTime),
					new SqlParameter("@EndDate", SqlDbType.DateTime),
					new SqlParameter("@CreateDate", SqlDbType.DateTime),
					new SqlParameter("@Comment", SqlDbType.NVarChar,200),
					new SqlParameter("@IsActive", SqlDbType.Bit,1),
					new SqlParameter("@EmployeeTitle", SqlDbType.NVarChar,64),
                                        new SqlParameter("@ScoreMin", SqlDbType.Int,4),
					new SqlParameter("@ScoreMax", SqlDbType.Int,4)};
            parameters[0].Value = model.Theme;
            parameters[1].Value = model.Category;
            parameters[2].Value = model.EmployeeId;
            parameters[3].Value = model.EmployeeName;
            parameters[4].Value = model.RoleId;
            parameters[5].Value = model.StartDate;
            parameters[6].Value = model.EndDate;
            parameters[7].Value = model.CreateDate;
            parameters[8].Value = model.Comment;
            parameters[9].Value = model.IsActive;
            parameters[10].Value = model.EmployeeTitle;
            parameters[11].Value = model.ScoreMin;
            parameters[12].Value = model.ScoreMax;

            try
            {
                object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
                if (obj == null)
                {
                    return 0;
                }
                else
                {
                    model.Id = Convert.ToInt32(obj);
                    List<DictionaryEntry> lists = new List<DictionaryEntry>();
                    foreach (Model.VoteItem itm in itemList)
                    {
                        lists.Add(AddVoteItem(itm, model.Id));
                    }
                    foreach (Model.VoteSubOffice sub in subOffices)
                    {
                        lists.Add(AddSubOffice(sub, model.Id));
                    }

                    DbHelperSQL.ExecuteSqlTran(lists);
                    return model.Id;
                }
            }
            catch (Exception ex) { errorString = ex.Message; return 0; }
        }
        /// <summary>
        /// 增加一条数据
        /// </summary>
        public DictionaryEntry AddVoteItem(Model.VoteItem model, int voteId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into VoteItem(");
            strSql.Append("VoteId,Sort,Value)");
            strSql.Append(" values (");
            strSql.Append("@VoteId,@Sort,@Value)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@VoteId", SqlDbType.Int,4),
					new SqlParameter("@Sort", SqlDbType.Int,4),
					new SqlParameter("@Value", SqlDbType.NVarChar,128)};
            parameters[0].Value = voteId;
            parameters[1].Value = model.Sort;
            parameters[2].Value = model.Value;

            DictionaryEntry myDe = new DictionaryEntry();
            myDe.Key = strSql.ToString();
            myDe.Value = parameters;
            return myDe;
        }

        public DictionaryEntry AddSubOffice(Model.VoteSubOffice model, int voteId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into VoteSubOffice(");
            strSql.Append("VoteId,SubOfficeId,Sort)");
            strSql.Append(" values (");
            strSql.Append("@VoteId,@SubOfficeId,@Sort)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@VoteId", SqlDbType.Int,4),
					new SqlParameter("@SubOfficeId", SqlDbType.Int,4),
					new SqlParameter("@Sort", SqlDbType.Int,4)};
            parameters[0].Value = voteId;
            parameters[1].Value = model.SubOfficeId;
            parameters[2].Value = model.Sort;
            DictionaryEntry myDe = new DictionaryEntry();
            myDe.Key = strSql.ToString();
            myDe.Value = parameters;
            return myDe;
        }

        public Boolean Update(Model.Vote model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update Vote set ");
            strSql.Append("Theme=@Theme,");
            strSql.Append("Category=@Category,");
            strSql.Append("EmployeeId=@EmployeeId,");
            strSql.Append("EmployeeName=@EmployeeName,");
            strSql.Append("RoleId=@RoleId,");
            strSql.Append("StartDate=@StartDate,");
            strSql.Append("EndDate=@EndDate,");
            strSql.Append("CreateDate=@CreateDate,");
            strSql.Append("Comment=@Comment,");
            strSql.Append("IsActive=@IsActive,");
            strSql.Append("EmployeeTitle=@EmployeeTitle");
            strSql.Append(" where Id=@Id");
            SqlParameter[] parameters = {
					new SqlParameter("@Theme", SqlDbType.NVarChar,128),
					new SqlParameter("@Category", SqlDbType.Int,4),
					new SqlParameter("@EmployeeId", SqlDbType.NVarChar,64),
					new SqlParameter("@EmployeeName", SqlDbType.NVarChar,64),
					new SqlParameter("@RoleId", SqlDbType.NVarChar,1024),
					new SqlParameter("@StartDate", SqlDbType.DateTime),
					new SqlParameter("@EndDate", SqlDbType.DateTime),
					new SqlParameter("@CreateDate", SqlDbType.DateTime),
					new SqlParameter("@Comment", SqlDbType.NVarChar,200),
					new SqlParameter("@IsActive", SqlDbType.Bit,1),
					new SqlParameter("@EmployeeTitle", SqlDbType.NVarChar,64),
					new SqlParameter("@Id", SqlDbType.Int,4)};
            parameters[0].Value = model.Theme;
            parameters[1].Value = model.Category;
            parameters[2].Value = model.EmployeeId;
            parameters[3].Value = model.EmployeeName;
            parameters[4].Value = model.RoleId;
            parameters[5].Value = model.StartDate;
            parameters[6].Value = model.EndDate;
            parameters[7].Value = model.CreateDate;
            parameters[8].Value = model.Comment;
            parameters[9].Value = model.IsActive;
            parameters[10].Value = model.EmployeeTitle;
            parameters[11].Value = model.Id;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 批量删除数据
        /// </summary>
        public bool DeleteList(string ids)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from Vote ");
            strSql.Append(" where Id in (" + ids + ");  ");
            strSql.Append("delete from VoteItem ");
            strSql.Append(" where VoteId in (" + ids + ");  ");
            strSql.Append("delete from VoteSubOffice ");
            strSql.Append(" where VoteId in (" + ids + ");  "); 
            //删除会议关联关系
            strSql.Append("delete from MeetingSurvey ");
            strSql.Append(" where VoteId in (" + ids + ");  "); 
            //结果
            strSql.Append("delete from VoteResult ");
            strSql.Append(" where VoteId in (" + ids + ");  ");
            strSql.Append("delete from VoteResult_Office ");
            strSql.Append(" where VoteId in (" + ids + ");  "); 
            
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 更新投票选项
        /// </summary>
        /// <param name="list"></param>
        /// <param name="delIds"></param>
        /// <returns></returns>
        public bool UpdateVoteItem(int voteId, List<Model.VoteItem> list, string delIds)
        {
            List<string> sqlList = new List<string>();
            if (list != null && list.Count > 0)
            {
                list.ForEach(x =>
                {
                    if (x.Id == 0)
                    {
                        sqlList.Add(string.Format(insertVoteItem, voteId, x.Value));
                    }
                    else
                    {
                        sqlList.Add(string.Format(updateVoteItem, x.Id, x.Value));
                    }
                });
            }

            if (delIds.Length > 0)
            {
                sqlList.Add(string.Format(delVoteItemList, delIds));
            }

            if (sqlList.Count == 0)
            {
                errorString = "没有可更新的记录！";
                return false;
            }

            try
            {
                int obj = DbHelperSQL.ExecuteSqlTran(sqlList);
                if (obj == 0)
                {
                    errorString = "更新行数为零";
                    return false;
                }

                return true;
            }
            catch (Exception ex) { errorString = ex.Message; return false; }
        }

        /// <summary>
        /// 获取会议的投票
        /// </summary>
        /// <param name="meetingId"></param>
        /// <returns></returns>
        public string GetVotesByMeeting(int meetingId)
        {
            JsonResponse response = new JsonResponse();
            try
            {
                //string sql = "Select v.*,(select count(vr.Id) from VoteResult vr where vr.VoteId = v.Id) as SelectedCount From Vote v inner join MeetingSurvey ms on v.Id = ms.VoteId where ms.MeetingId = {0}";
                string sql = "Select v.* From Vote v inner join MeetingSurvey ms on v.Id = ms.VoteId where ms.MeetingId = {0}";
                DataSet ds = DbHelperSQL.Query(string.Format(sql, meetingId));
                //
                if (ds == null)
                {
                    response.success = false;
                    response.Message = DbHelperSQL.errorString;
                }
                else
                {
                    response.success = true;
                    response.ResponseData = ds.Tables[0];
                }

                IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
                timeConverter.DateTimeFormat = "yyyy'-'MM'-'dd' 'HH':'mm':'ss";

                return JsonConvert.SerializeObject(response, timeConverter);
            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                response.success = false;
                response.Message = errorString;
                return JsonConvert.SerializeObject(response);
            }
        }

        /// <summary>
        /// 获取用户参与的投票
        /// </summary>
        /// <param name="empId"></param>
        /// <returns></returns>
        public string GetVotesByEmployee(string empId)
        {
            JsonResponse response = new JsonResponse();
            try
            {
                //string sql = "Select distinct v.Id,v.Theme,v.Category,vr.MeetingId,m.Name as MeetingName,v.EmployeeName,(select count(vr.Id) from VoteResult vr where vr.VoteId = v.Id) as SelectedCount From Vote v inner join VoteResult vr on v.Id = vr.VoteId inner join Meeting m on vr.MeetingId = m.Id where vr.EmployeeId = '{0}'";
                string sql = @"Select distinct v.Id,v.Theme,v.Category,m.Id as MeetingId,m.Name as MeetingName,v.EmployeeName
                                From Vote v left outer join VoteResult vr on v.Id = vr.VoteId 
                                left outer join VoteResult_Office vo on v.Id = vo.VoteId 
                                inner join Meeting m on vr.MeetingId = m.Id or vo.MeetingId = m.Id
                                where vr.EmployeeId = '{0}' or vo.EmployeeId = '{0}'";
                DataSet ds = DbHelperSQL.Query(string.Format(sql, empId));
                //
                if (ds == null)
                {
                    response.success = false;
                    response.Message = DbHelperSQL.errorString;
                }
                else
                {
                    response.success = true;
                    response.ResponseData = ds.Tables[0];
                }

                IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
                timeConverter.DateTimeFormat = "yyyy'-'MM'-'dd' 'HH':'mm':'ss";

                return JsonConvert.SerializeObject(response, timeConverter);
            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                response.success = false;
                response.Message = errorString;
                return JsonConvert.SerializeObject(response);
            }
        }


        /// <summary>
        /// 获取投票的投票选项
        /// </summary>
        /// <returns></returns>
        public string GetVoteItemsByVoteId(int voteId)
        {
            JsonResponse response = new JsonResponse();
            try
            {
                string sql = "Select * From VoteItem where VoteId = {0} order by Sort";
                DataSet ds = DbHelperSQL.Query(string.Format(sql, voteId));
                //
                if (ds == null)
                {
                    response.success = false;
                    response.Message = DbHelperSQL.errorString;
                }
                else
                {
                    response.success = true;
                    response.ResponseData = ds.Tables[0];
                }

                IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
                timeConverter.DateTimeFormat = "yyyy'-'MM'-'dd' 'HH':'mm':'ss";

                return JsonConvert.SerializeObject(response, timeConverter);
            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                response.success = false;
                response.Message = errorString;
                return JsonConvert.SerializeObject(response);
            }
        }

        /// <summary>
        /// 添加投票及投票选项
        /// </summary>
        /// <param name="model">投票及投票选项的json字符串</param>
        /// <returns></returns>
        public string AddVote(string model, int meetingId)
        {
            JsonResponse response = new JsonResponse();

            try
            {
                var jSetting = new JsonSerializerSettings();
                jSetting.NullValueHandling = NullValueHandling.Ignore;
                Model.Vote v = JsonConvert.DeserializeObject<Model.Vote>(model, jSetting);

                //首先插入投票，返回主键
                StringBuilder strSql = new StringBuilder();
                strSql.Append("insert into Vote(");
                strSql.Append("Theme,Category,EmployeeId,EmployeeTitle,EmployeeName,RoleId)");
                strSql.Append(" values (");
                strSql.Append("@Theme,@Category,@EmployeeId,@EmployeeTitle,@EmployeeName,@RoleId)");
                strSql.Append(";select @@IDENTITY");
                SqlParameter[] parameters = {
					new SqlParameter("@Theme", SqlDbType.NVarChar,128),
					new SqlParameter("@Category", SqlDbType.Int,4),
					new SqlParameter("@EmployeeId", SqlDbType.NVarChar,64),
					new SqlParameter("@EmployeeTitle", SqlDbType.NVarChar,64),
					new SqlParameter("@EmployeeName", SqlDbType.NVarChar,64),
					new SqlParameter("@RoleId", SqlDbType.NVarChar,1024)};
                parameters[0].Value = v.Theme;
                parameters[1].Value = v.Category;
                parameters[2].Value = v.EmployeeId;
                parameters[3].Value = v.EmployeeTitle;
                parameters[4].Value = v.EmployeeName;
                parameters[5].Value = v.RoleId;

                object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
                if (obj == null)
                {
                    response.success = false;
                    response.Message = DbHelperSQL.errorString;
                }
                else
                {
                    v.Id = Convert.ToInt32(obj);
                    //插入投票选项
                    string sql = "INSERT INTO [VoteItem]([VoteId],[Sort],[Value])VALUES({0},{1},'{2}')";
                    List<string> sqlList = new List<string>();
                    v.Items.ForEach(x =>
                    {
                        sqlList.Add(string.Format(sql, v.Id, x.Sort, x.Value));
                    });
                    //插入与会议的关联关系
                    sqlList.Add(string.Format("INSERT INTO MeetingSurvey([VoteId],[MeetingId])VALUES({0},{1})", v.Id, meetingId));
                    obj = DbHelperSQL.ExecuteSqlTran(sqlList);
                    if (Convert.ToInt32(obj) == 0)
                    {
                        response.success = false;
                        response.Message = DbHelperSQL.errorString;
                    }
                    else
                    {
                        response.success = true;
                        response.Message = "保存成功";
                    }
                }

                return JsonConvert.SerializeObject(response);
            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                response.success = false;
                response.Message = errorString;
                return JsonConvert.SerializeObject(response);
            }
        }

        /// <summary>
        /// 提交投票结果
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public string SubmitResult(string model)
        {
            JsonResponse response = new JsonResponse();
            try
            {
                var jSetting = new JsonSerializerSettings();
                jSetting.NullValueHandling = NullValueHandling.Ignore;
                Model.VoteResult r = JsonConvert.DeserializeObject<Model.VoteResult>(model, jSetting);

                //插入投票结果
                string sql = "INSERT INTO VoteResult([MeetingId],[VoteId],[SelectValue],[EmployeeId],[EmployeeName],[EmployeeTitle],[RoleId])VALUES({0},{1},'{2}','{3}','{4}','{5}','{6}')";

                int obj = DbHelperSQL.ExecuteSql(string.Format(sql, r.MeetingId, r.VoteId, r.SelectValue, r.EmployeeId, r.EmployeeName, r.EmployeeTitle, r.RoleId));
                if (obj == 0)
                {
                    response.success = false;
                    response.Message = DbHelperSQL.errorString;
                }
                else
                {
                    response.success = true;
                    response.Message = "保存成功";
                }

                return JsonConvert.SerializeObject(response);
            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                response.success = false;
                response.Message = errorString;
                return JsonConvert.SerializeObject(response);
            }
        }

        /// <summary>
        /// 获取投票结果
        /// </summary>
        /// <param name="voteId"></param>
        /// <returns></returns>
        public string GetVoteResult(int meetingId, int voteId)
        {
            JsonResponse response = new JsonResponse();
            try
            {
                //首先获取投票选项
                //获取投票结果
                string sql = @" SELECT * FROM [VoteResult] WHERE VoteId = {0} and MeetingId = {1}
                                SELECT * FROM [VoteItem] WHERE VoteId = {0} Order by Sort";

                DataSet ds = DbHelperSQL.Query(string.Format(sql, voteId, meetingId));
                //
                if (ds == null)
                {
                    response.success = false;
                    response.Message = DbHelperSQL.errorString;
                }
                else
                {
                    response.success = true;

                    #region
                    List<Model.VoteResult> rList = new List<VoteResult>();
                    int rowsCount = ds.Tables[0].Rows.Count;
                    if (rowsCount > 0)
                    {
                        Model.VoteResult vr;
                        for (int n = 0; n < rowsCount; n++)
                        {
                            vr = new Model.VoteResult();

                            if (ds.Tables[0].Rows[n]["MeetingId"] != null && ds.Tables[0].Rows[n]["MeetingId"].ToString() != "")
                            {
                                vr.MeetingId = int.Parse(ds.Tables[0].Rows[n]["MeetingId"].ToString());
                            }
                            if (ds.Tables[0].Rows[n]["VoteId"] != null && ds.Tables[0].Rows[n]["VoteId"].ToString() != "")
                            {
                                vr.VoteId = int.Parse(ds.Tables[0].Rows[n]["VoteId"].ToString());
                            }
                            if (ds.Tables[0].Rows[n]["SelectValue"] != null && ds.Tables[0].Rows[n]["SelectValue"].ToString() != "")
                            {
                                vr.SelectValue = ds.Tables[0].Rows[n]["SelectValue"].ToString();
                            }
                            if (ds.Tables[0].Rows[n]["EmployeeId"] != null && ds.Tables[0].Rows[n]["EmployeeId"].ToString() != "")
                            {
                                vr.EmployeeId = ds.Tables[0].Rows[n]["EmployeeId"].ToString();
                            }
                            rList.Add(vr);
                        }
                    }

                    List<Model.VoteItem> viList = new List<VoteItem>();
                    rowsCount = ds.Tables[1].Rows.Count;
                    if (rowsCount > 0)
                    {
                        Model.VoteItem vi;
                        for (int n = 0; n < rowsCount; n++)
                        {
                            vi = new Model.VoteItem();
                            if (ds.Tables[1].Rows[n]["VoteId"] != null && ds.Tables[1].Rows[n]["VoteId"].ToString() != "")
                            {
                                vi.VoteId = int.Parse(ds.Tables[1].Rows[n]["VoteId"].ToString());
                            }
                            if (ds.Tables[1].Rows[n]["Sort"] != null && ds.Tables[1].Rows[n]["Sort"].ToString() != "")
                            {
                                vi.Sort = int.Parse(ds.Tables[1].Rows[n]["Sort"].ToString());
                            }
                            if (ds.Tables[1].Rows[n]["Value"] != null && ds.Tables[1].Rows[n]["Value"].ToString() != "")
                            {
                                vi.Value = ds.Tables[1].Rows[n]["Value"].ToString();
                            }
                            vi.SelectedCount = rList.Count(r =>
                            {
                                return r.ValueList.Contains(ds.Tables[1].Rows[n]["Id"].ToString());
                            });
                            if (rList.Count == 0)
                            {
                                vi.SelectedPer = "0";
                            }
                            else
                            {
                                vi.SelectedPer = ((decimal)vi.SelectedCount * 100 / rList.Count).ToString() + "%";
                            }
                            viList.Add(vi);
                        }
                    }
                    #endregion

                    response.ResponseData = viList;
                }

                return JsonConvert.SerializeObject(response);
            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                response.success = false;
                response.Message = errorString;
                return JsonConvert.SerializeObject(response);
            }
        }

        /// <summary>
        /// 获取投票信息以及关联信息
        /// </summary>
        /// <param name="voteId"></param>
        /// <returns></returns>
        public string GetVoteAndRelation(int voteId)
        {
            JsonResponse response = new JsonResponse();
            //获取议题
            string sql = @"select * from Vote where Id = '{0}'
                           select * from VoteItem where VoteId='{0}' Order By Sort
                           select sf.* from VoteSubOffice vo inner join SubOffice sf on vo.SubOfficeId = sf.Id where vo.VoteId = '{0}' Order By sf.Number";

            try
            {
                DataSet ds = DbHelperSQL.Query(string.Format(sql, voteId));
                //
                if (ds == null)
                {
                    response.success = false;
                    response.Message = DbHelperSQL.errorString;
                }
                else
                {
                    response.success = true;
                    Model.Vote model = new Model.Vote();

                    #region 转换投票基本信息
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        if (ds.Tables[0].Rows[0]["Id"] != null && ds.Tables[0].Rows[0]["Id"].ToString() != "")
                        {
                            model.Id = int.Parse(ds.Tables[0].Rows[0]["Id"].ToString());
                        }
                        if (ds.Tables[0].Rows[0]["Theme"] != null && ds.Tables[0].Rows[0]["Theme"].ToString() != "")
                        {
                            model.Theme = ds.Tables[0].Rows[0]["Theme"].ToString();
                        }
                        if (ds.Tables[0].Rows[0]["Category"] != null && ds.Tables[0].Rows[0]["Category"].ToString() != "")
                        {
                            model.Category = int.Parse(ds.Tables[0].Rows[0]["Category"].ToString());
                        }
                        if (ds.Tables[0].Rows[0]["EmployeeId"] != null && ds.Tables[0].Rows[0]["EmployeeId"].ToString() != "")
                        {
                            model.EmployeeId = ds.Tables[0].Rows[0]["EmployeeId"].ToString();
                        }
                        if (ds.Tables[0].Rows[0]["EmployeeName"] != null && ds.Tables[0].Rows[0]["EmployeeName"].ToString() != "")
                        {
                            model.EmployeeName = ds.Tables[0].Rows[0]["EmployeeName"].ToString();
                        }
                        if (ds.Tables[0].Rows[0]["EmployeeTitle"] != null && ds.Tables[0].Rows[0]["EmployeeTitle"].ToString() != "")
                        {
                            model.EmployeeTitle = ds.Tables[0].Rows[0]["EmployeeTitle"].ToString();
                        }
                        if (ds.Tables[0].Rows[0]["RoleId"] != null && ds.Tables[0].Rows[0]["RoleId"].ToString() != "")
                        {
                            model.RoleId = ds.Tables[0].Rows[0]["RoleId"].ToString();
                        }
                        if (ds.Tables[0].Rows[0]["StartDate"] != null && ds.Tables[0].Rows[0]["StartDate"].ToString() != "")
                        {
                            model.StartDate = DateTime.Parse(ds.Tables[0].Rows[0]["StartDate"].ToString());
                        }
                        if (ds.Tables[0].Rows[0]["EndDate"] != null && ds.Tables[0].Rows[0]["EndDate"].ToString() != "")
                        {
                            model.EndDate = DateTime.Parse(ds.Tables[0].Rows[0]["EndDate"].ToString());
                        }
                        if (ds.Tables[0].Rows[0]["CreateDate"] != null && ds.Tables[0].Rows[0]["CreateDate"].ToString() != "")
                        {
                            model.CreateDate = DateTime.Parse(ds.Tables[0].Rows[0]["CreateDate"].ToString());
                        }
                        if (ds.Tables[0].Rows[0]["Comment"] != null && ds.Tables[0].Rows[0]["Comment"].ToString() != "")
                        {
                            model.Comment = ds.Tables[0].Rows[0]["Comment"].ToString();
                        }
                        if (ds.Tables[0].Rows[0]["IsActive"] != null && ds.Tables[0].Rows[0]["IsActive"].ToString() != "")
                        {
                            if ((ds.Tables[0].Rows[0]["IsActive"].ToString() == "1") || (ds.Tables[0].Rows[0]["IsActive"].ToString().ToLower() == "true"))
                            {
                                model.IsActive = true;
                            }
                            else
                            {
                                model.IsActive = false;
                            }
                        }
                        if (ds.Tables[0].Rows[0]["ScoreMin"] != null && ds.Tables[0].Rows[0]["ScoreMin"].ToString() != "")
                        {
                            model.ScoreMin = int.Parse(ds.Tables[0].Rows[0]["ScoreMin"].ToString());
                        }
                        if (ds.Tables[0].Rows[0]["ScoreMax"] != null && ds.Tables[0].Rows[0]["ScoreMax"].ToString() != "")
                        {
                            model.ScoreMax = int.Parse(ds.Tables[0].Rows[0]["ScoreMax"].ToString());
                        }
                    }
                    #endregion

                    #region 转换投票选项
                    model.Items = new List<VoteItem>();
                    int rowsCount = ds.Tables[1].Rows.Count;
                    if (rowsCount > 0)
                    {
                        Model.VoteItem item;
                        for (int n = 0; n < rowsCount; n++)
                        {
                            item = new Model.VoteItem();
                            if (ds.Tables[1].Rows[n]["Id"] != null && ds.Tables[1].Rows[n]["Id"].ToString() != "")
                            {
                                item.Id = int.Parse(ds.Tables[1].Rows[n]["Id"].ToString());
                            }
                            if (ds.Tables[1].Rows[n]["VoteId"] != null && ds.Tables[1].Rows[n]["VoteId"].ToString() != "")
                            {
                                item.VoteId = int.Parse(ds.Tables[1].Rows[n]["VoteId"].ToString());
                            }
                            if (ds.Tables[1].Rows[n]["Sort"] != null && ds.Tables[1].Rows[n]["Sort"].ToString() != "")
                            {
                                item.Sort = int.Parse(ds.Tables[1].Rows[n]["Sort"].ToString());
                            }
                            if (ds.Tables[1].Rows[n]["Value"] != null && ds.Tables[1].Rows[n]["Value"].ToString() != "")
                            {
                                item.Value = ds.Tables[1].Rows[n]["Value"].ToString();
                            }
                            model.Items.Add(item);
                        }
                    }
                    #endregion

                    #region 转换投票分局
                    model.Offices = new List<Model.SubOffice>();
                    rowsCount = ds.Tables[2].Rows.Count;
                    if (rowsCount > 0)
                    {
                        Model.SubOffice office;
                        for (int n = 0; n < rowsCount; n++)
                        {
                            office = new Meeting.Model.SubOffice();
                            if (ds.Tables[2].Rows[n]["Id"] != null && ds.Tables[2].Rows[n]["Id"].ToString() != "")
                            {
                                office.Id = int.Parse(ds.Tables[2].Rows[n]["Id"].ToString());
                            }
                            if (ds.Tables[2].Rows[n]["Number"] != null && ds.Tables[2].Rows[n]["Number"].ToString() != "")
                            {
                                office.Number = int.Parse(ds.Tables[2].Rows[n]["Number"].ToString());
                            }
                            if (ds.Tables[2].Rows[n]["Name"] != null && ds.Tables[2].Rows[n]["Name"].ToString() != "")
                            {
                                office.Name = ds.Tables[2].Rows[n]["Name"].ToString();
                            }
                            model.Offices.Add(office);
                        }
                    }
                    #endregion

                    #region 转换投票结果
                    model.Results = new List<VoteResult_Office>();
                    Model.VoteResult_Office result;
                    for (int i = 0; i < model.Offices.Count; i++)
                    {
                        for (int j = 0; j < model.Items.Count; j++)
                        {
                            result = new VoteResult_Office();
                            result.SubOfficeId = model.Offices[i].Id;
                            result.VoteId = model.Id;
                            result.VoteItemId = model.Items[j].Id;
                            result.SelectValue = model.ScoreMax;
                            model.Results.Add(result);
                        }
                    }
                    #endregion
                    response.ResponseData = model;
                }

                IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
                timeConverter.DateTimeFormat = "yyyy'-'MM'-'dd' 'HH':'mm':'ss";

                return JsonConvert.SerializeObject(response, timeConverter);
            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                response.success = false;
                response.Message = errorString;
                return JsonConvert.SerializeObject(response); ;
            }
        }

        /// <summary>
        /// 提交分局打分投票结果
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public string SubmitResult_Office(string model)
        {
            JsonResponse response = new JsonResponse();
            try
            {
                var jSetting = new JsonSerializerSettings();
                jSetting.NullValueHandling = NullValueHandling.Ignore;
                List<Model.VoteResult_Office> list = JsonConvert.DeserializeObject<List<Model.VoteResult_Office>>(model, jSetting);
                if (list.Count == 0)
                {
                    response.success = true;
                    response.Message = "没有可提交的结果！";
                }
                else
                {
                    List<string> sqlList = new List<string>();
                    //删除之前用户在本次会议提交过的投票结果
                    sqlList.Add(string.Format("delete from VoteResult_Office where MeetingId = {0} and EmployeeId = '{1}'", list[0].MeetingId, list[0].EmployeeId));
                    //插入投票结果
                    string sql = @"INSERT INTO VoteResult_Office([MeetingId],[VoteId],[SubOfficeId],[VoteItemId],[SelectValue],[EmployeeId],[EmployeeName],[EmployeeTitle],[RoleId])
                                VALUES({0},{1},{2},{3},{4},'{5}','{6}','{7}','{8}')";
                    foreach (VoteResult_Office r in list)
                    {
                        sqlList.Add(string.Format(sql, r.MeetingId, r.VoteId, r.SubOfficeId, r.VoteItemId, r.SelectValue, r.EmployeeId, r.EmployeeName, r.EmployeeTitle, r.RoleId));
                    }
                    int rows = DbHelperSQL.ExecuteSqlTran(sqlList);
                    if (rows > 0)
                    {
                        response.success = true;
                        response.Message = "保存成功";
                    }
                    else
                    {
                        response.success = false;
                        response.Message = DbHelperSQL.errorString;
                    }
                }

                return JsonConvert.SerializeObject(response);
            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                response.success = false;
                response.Message = errorString;
                return JsonConvert.SerializeObject(response);
            }
        }

        /// <summary>
        /// 获取投票结果
        /// </summary>
        /// <param name="voteId"></param>
        /// <returns></returns>
        public string GetVoteResult_Office(int meetingId, int voteId)
        {
            JsonResponse response = new JsonResponse();
            try
            {
                //首先获取投票选项
                //获取投票结果
                string sql = @"SELECT vo.[MeetingId],vo.[VoteId],vo.[SubOfficeId],vo.[VoteItemId],so.Name as SubOfficeName,vi.Value as ItemName,
                                count(SelectValue) as PeopleNumber,min([SelectValue]) as ScoreMin,MAX(SelectValue)as ScoreMax,AVG(SelectValue) as ScoreAvg      
                                FROM [VoteResult_Office] vo inner join Meeting m on vo.MeetingId = m.Id
                                inner join VoteItem vi on vi.Id = vo.VoteItemId
                                inner join SubOffice so on so.Id = vo.SubOfficeId
                                where vo.MeetingId = {0} and vo.VoteId = {1}
                                group by vo.[MeetingId],vo.[VoteId],vo.[SubOfficeId],vo.[VoteItemId],so.Name ,vi.Value,so.Number,vi.Sort
                                order by so.Number,vi.Sort";
                DataSet ds = DbHelperSQL.Query(string.Format(sql, meetingId, voteId));
                //
                if (ds == null)
                {
                    response.success = false;
                    response.Message = DbHelperSQL.errorString;
                }
                else
                {
                    response.success = true;
                    response.ResponseData = ds.Tables[0];
                }

                return JsonConvert.SerializeObject(response);
            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                response.success = false;
                response.Message = errorString;
                return JsonConvert.SerializeObject(response);
            }
        }
    }
}
